﻿using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Style;

namespace Customize.Excel.Tests
{
    public class EPPlusExcel
    {

        #region 1.使用EPPlus创建EXCEL
        public void Test1()
        {
            //说明：XSSFWorkbook 用于创建  .xlsx                 
            ExcelPackage package = new ExcelPackage();

            //2.创建Workbook中的Sheet  
            package.Workbook.Worksheets.Add("Sheet1");

            ExcelWorkbook workbook = package.Workbook;
            //a.Office的属性
            workbook.Properties.Category = "类别";
            workbook.Properties.Author = "作者";
            workbook.Properties.Comments = "备注";
            workbook.Properties.Company = "公司";
            workbook.Properties.Keywords = "关键字";
            workbook.Properties.Manager = "管理者";
            workbook.Properties.Status = "内容状态";
            workbook.Properties.Subject = "主题";
            workbook.Properties.Title = "标题";
            workbook.Properties.LastModifiedBy = "最后一次保存者";

            ExcelWorksheet sheet1 = workbook.Worksheets["Sheet1"];//取得Sheet1  
            sheet1.DefaultColWidth = 10; //默认列宽
            sheet1.DefaultRowHeight = 30; //默认行高
            sheet1.TabColor = Color.Blue; //Sheet Tab的颜色
            sheet1.Cells.Style.WrapText = true; //单元格文字自动换行

            //3.创建Sheet中的Row  
            ExcelRow row = sheet1.Row(1);
            //b.对行列的增删操作
            //sheet1.InsertRow(1, 10);
            //sheet1.DeleteRow(2, 8);
            //sheet1.InsertColumn(2, 12);
            //sheet1.DeleteColumn(3, 5);
            //c.设置指定行或列的样式(宽、高、隐藏、自动换行、数字格式、锁定等)
            //sheet1.Column(1).Width = 10;
            //sheet1.Row(1).Height = 30;
            //sheet1.Column(1).Hidden = true;
            //sheet1.Row(1).Hidden = true;
            //sheet1.Column(1).Style.WrapText = true;
            //sheet1.Column(1).Style.Numberformat.Format = "$#,###.00";
            //sheet1.Row(1).Style.Locked = true;
            //d.自适应宽度设置：
            //sheet1.Column(1).AutoFit();
            //sheet1.Column(1).AutoFit(10);
            //sheet1.Column(1).AutoFit(10, 100);   //未传入MinimumWidth时，使用Sheet的DefaultColWidth作为最小值，此时若没有提前对DefaultColWidth进行设置就会报错,此方法时对自动换行和合并的单元格无效

            //e.从二维数据集合中装载数据
            //LoadFromCollection<T>(IEnumerable<T> Collection);
            //LoadFromDataReader(IDataReader Reader, bool PrintHeaders);
            //LoadFromText(FileInfo TextFile);
            //f.样式包括字体、颜色、对齐、边框
            //sheet1.Column(1).Style.Fill.PatternType = ExcelFillStyle.Solid;
            //sheet1.Column(1).Style.Fill.BackgroundColor.SetColor(Color.Gray);
            //在设置Style.Fill的其他属性之前，必须设置PatternType 
            //设置了Style.Fill.PatternType之后，必须设置Style.Fill.BackgroundColor，否则单元格背景为黑色
            //注意Style.Fill.BackgroundColor是个只读属性，需要通过SetColor()方法设置颜色
            //sheet1.Column(1).Font.Color.SetColor(Color.Red);
            //sheet1.Column(1).HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;

            //g .隐藏
            //sheet1.Hidden = eWorkSheetHidden.Hidden;//隐藏sheet
            //sheet1.Column(1).Hidden = true;//隐藏某一列
            //sheet1.Row(1).Hidden = true;//隐藏某一行

            //

            //ExcelShape shape = sheet1.Drawings.AddShape("shape", eShapeStyle.Rect);//插入形状
            //shape.Font.Color = Color.Red;//设置形状的字体颜色
            //shape.Font.Size = 15;//字体大小
            //shape.Font.Bold = true;//字体粗细
            //shape.Fill.Style = eFillStyle.NoFill;//设置形状的填充样式
            //shape.Border.Fill.Style = eFillStyle.NoFill;//边框样式
            //shape.SetPosition(200, 300);//形状的位置
            //shape.SetSize(80, 30);//形状的大小
            //shape.Text = "test";//形状的内容

            //4.给Cell赋值  
            sheet1.Cells[1, 1].Value = true;
            sheet1.Cells[1, 2].Value = DateTime.Now;
            sheet1.Cells[1, 3].Value = 9.32;
            sheet1.Cells[1, 4].Value = "Hello World！";


            //5.保存  
            using (FileStream fs = new FileStream(@"E:\EPPlus1.xlsx", FileMode.Create))
            {
                package.SaveAs(fs);
            }
        }
        #endregion

        #region 2.设置EXCEL列宽、行高与合并单元格
        public void Test2()
        {
            //说明：设置列宽、行高与合并单元格  

            //1.创建EXCEL中的Workbook           
            ExcelPackage package = new ExcelPackage();

            //2.创建Workbook中的Sheet          
            ExcelWorksheet mysheet = package.Workbook.Worksheets[1];

            //4.合并单元格区域 例： 第1行到第1行 第2列到第3列围成的矩形区域  
            mysheet.Cells[1, 2, 1, 3].Merge = true;
            //5.创建Row中的Cell并赋值  
            //http://www.cnblogs.com/rumeng/p/3785775.html

            mysheet.Cells[1, 1].Value = "1-1";
            mysheet.Cells[1, 2].Value = "1-2";
            mysheet.Cells[1, 3].Value = "1-2";
            mysheet.Cells[1, 4].Value = "1-3";


            mysheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小

            mysheet.Row(1).Height = 15;//设置行高
            mysheet.Row(1).CustomHeight = true;//自动调整行高
            mysheet.Column(1).Width = 15;//设置列宽


            //6.设置列宽
            mysheet.Column(2).AutoFit(10);

            //7.设置行高 
            mysheet.Row(2).Height = 20;

            //5.保存         
            FileStream file = new FileStream(@"E:\myworkbook2.xlsx", FileMode.Create);
            package.SaveAs(file);
            file.Close();
        }
        #endregion

        #region 3.设置EXCEL单元格对齐方式
        public void Test3()
        {

            //说明：设置单元格对齐方式  

            //1.创建EXCEL中的Workbook           
            ExcelPackage package = new ExcelPackage();

            //2.创建Workbook中的Sheet          
            ExcelWorksheet mysheet = package.Workbook.Worksheets[1];

            //3.创建Row中的Cell并赋值  
            mysheet.DefaultRowHeight = 20;
            mysheet.Row(1).Height = 40;
            mysheet.Cells[1, 1].Value = "对齐方式";
            mysheet.Cells[1, 2].Value = "对齐方式";
            mysheet.Cells[1, 3].Value = "对齐方式";
            mysheet.Cells[1, 4].Value = "对齐方式";


            mysheet.Row(2).Height = 30;
            mysheet.Cells[2, 1].Value = "对齐方式";
            mysheet.Cells[2, 2].Value = "Shanghai is the largest city by population in ";
            mysheet.Cells[2, 3].Value = "对齐方式";
            mysheet.Cells[2, 4].Value = "对齐方式";

            //4.创建CellStyle  
            mysheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.General;
            //【General】数字、时间默认：右对齐；BOOL：默认居中；字符串：默认左对齐  

            mysheet.Cells[1, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            //【Left】左对齐 

            mysheet.Cells[1, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            //【Center】居中  

            mysheet.Cells[1, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
            //【Right】右对齐  

            mysheet.Cells[2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Fill;
            //【Fill】填充  

            mysheet.Cells[2, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Justify;
            //【Justify】两端对齐[会自动换行]（主要针对英文）  


            mysheet.Cells[2, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
            //【CenterSelection】跨列居中  


            mysheet.Cells[2, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Distributed;
            //【Distributed】分散对齐[会自动换行]  

            //【Tips】  

            //6.保存         
            FileStream file = new FileStream(@"E:\myworkbook3.xlsx", FileMode.Create);
            package.SaveAs(file);
            file.Close();
        }
        #endregion

        #region 4.设置EXCEL单元格背景与图案
        public void Test4()
        {
            //说明：设置单元格背景与图案  
            //1.创建EXCEL中的Workbook           
            ExcelPackage package = new ExcelPackage();

            //2.创建Workbook中的Sheet          
            ExcelWorksheet mysheet = package.Workbook.Worksheets[1];
            mysheet.DefaultRowHeight = 20;
            mysheet.DefaultColWidth = 56;

            //3.创建Row中的Cell并赋值  
            mysheet.Cells[1, 2].Value = "NoFill";
            mysheet.Cells[2, 2].Value = "SolidForeground";
            mysheet.Cells[3, 2].Value = "FineDots";
            mysheet.Cells[4, 2].Value = "AltBars";
            mysheet.Cells[5, 2].Value = "SparseDots";
            mysheet.Cells[6, 2].Value = "ThickHorizontalBands";
            mysheet.Cells[7, 2].Value = ("ThickVerticalBands");
            mysheet.Cells[8, 2].Value = ("ThickBackwardDiagonals");
            mysheet.Cells[9, 2].Value = ("ThickForwardDiagonals");
            mysheet.Cells[10, 2].Value = ("BigSpots");
            mysheet.Cells[11, 2].Value = ("Bricks");
            mysheet.Cells[12, 2].Value = ("ThinHorizontalBands");
            mysheet.Cells[13, 2].Value = ("ThinVerticalBands");
            mysheet.Cells[14, 2].Value = ("ThinBackwardDiagonals");
            mysheet.Cells[15, 2].Value = ("ThinForwardDiagonals");
            mysheet.Cells[16, 2].Value = ("Squares");
            mysheet.Cells[17, 2].Value = ("Diamonds");
            mysheet.Cells[18, 2].Value = ("LessDots");
            mysheet.Cells[19, 2].Value = ("LeastDots");

            //【Tips】  
            // 1.ForegroundColor(默认黑色)【前景颜色】
            // BackgroundColor(默认为前景颜色的反色)【背景颜色】
            // Pattern(必须指定，默认NoFill)【填充的图案】  
            // 2.演示中使用 【前景颜色】黑色 【背景颜色】白色  

            //4.创建CellStyle并应用于单元格    

            mysheet.Cells[1, 2].Style.Fill.BackgroundColor.SetColor(Color.White);
            mysheet.Cells[1, 2].Style.Fill.PatternColor.SetColor(Color.Black);
            mysheet.Cells[1, 2].Style.Fill.PatternType = ExcelFillStyle.None;


            mysheet.Cells[2, 2].Style.Fill.BackgroundColor.SetColor(Color.White);
            mysheet.Cells[2, 2].Style.Fill.PatternColor.SetColor(Color.Black);
            mysheet.Cells[2, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;

            mysheet.Cells[2, 2].Style.Fill.BackgroundColor.SetColor(Color.White);
            mysheet.Cells[2, 2].Style.Fill.PatternColor.SetColor(Color.Black);
            mysheet.Cells[2, 2].Style.Fill.PatternType = ExcelFillStyle.DarkDown;

            //5.保存         
            FileStream file = new FileStream(@"E:\myworkbook4.xlsx", FileMode.Create);
            package.SaveAs(file);
            file.Close();
        }
        #endregion

        #region 5.设置EXCEL单元格边框
        public void Test5()
        {
            //说明：设置单元格边框  
            //1.创建EXCEL中的Workbook           
            ExcelPackage package = new ExcelPackage();

            //2.创建Workbook中的Sheet          
            ExcelWorksheet mysheet = package.Workbook.Worksheets[1];

            //3.创建Row中的Cell并赋值  
            mysheet.Cells[1, 2].Value = ("Thin");
            mysheet.Cells[2, 2].Value = ("Medium");
            mysheet.Cells[3, 2].Value = ("Dashed");
            mysheet.Cells[4, 2].Value = ("Dotted");
            mysheet.Cells[5, 2].Value = ("Thick");
            mysheet.Cells[6, 2].Value = ("Double");
            mysheet.Cells[7, 2].Value = ("Hair");
            mysheet.Cells[8, 2].Value = ("MediumDashed");
            mysheet.Cells[9, 2].Value = ("DashDot");
            mysheet.Cells[10, 2].Value = ("MediumDashDot");
            mysheet.Cells[11, 2].Value = ("DashDotDot");
            mysheet.Cells[12, 2].Value = ("MediumDashDotDot");
            mysheet.Cells[13, 2].Value = ("None");


            //4.创建CellStyle  

            mysheet.Cells[1, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));//设置单元格所有边框
            // mysheet.Cells[1, 2].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//单独设置单元格底部边框样式和颜色（上下左右均可分开设置）
            //mysheet.Cells[1, 2].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191));

            mysheet.Cells[2, 2].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.FromArgb(191, 191, 191));
            mysheet.Cells[3, 2].Style.Border.BorderAround(ExcelBorderStyle.Dashed, Color.FromArgb(191, 191, 191));
            mysheet.Cells[4, 2].Style.Border.BorderAround(ExcelBorderStyle.Dotted, Color.FromArgb(191, 191, 191));
            mysheet.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thick, Color.FromArgb(191, 191, 191));
            mysheet.Cells[6, 2].Style.Border.BorderAround(ExcelBorderStyle.Double, Color.FromArgb(191, 191, 191));
            mysheet.Cells[7, 2].Style.Border.BorderAround(ExcelBorderStyle.Hair, Color.FromArgb(191, 191, 191));
            mysheet.Cells[8, 2].Style.Border.BorderAround(ExcelBorderStyle.MediumDashed, Color.FromArgb(191, 191, 191));
            mysheet.Cells[9, 2].Style.Border.BorderAround(ExcelBorderStyle.DashDot, Color.FromArgb(191, 191, 191));
            mysheet.Cells[10, 2].Style.Border.BorderAround(ExcelBorderStyle.MediumDashDot, Color.FromArgb(191, 191, 191));
            mysheet.Cells[11, 2].Style.Border.BorderAround(ExcelBorderStyle.DashDotDot, Color.FromArgb(191, 191, 191));
            mysheet.Cells[12, 2].Style.Border.BorderAround(ExcelBorderStyle.MediumDashDotDot, Color.FromArgb(191, 191, 191));
            mysheet.Cells[13, 2].Style.Border.BorderAround(ExcelBorderStyle.None, Color.FromArgb(191, 191, 191));


            //4.保存         
            FileStream file = new FileStream(@"E:\myworkbook5.xlsx", FileMode.Create);
            package.SaveAs(file);
            file.Close();
        }
        #endregion

        #region 6.设置字体
        public void Test6()
        {
            //说明：设置字体  
            //1.创建EXCEL中的Workbook           
            ExcelPackage package = new ExcelPackage();

            //2.创建Workbook中的Sheet          
            ExcelWorksheet mysheet = package.Workbook.Worksheets[1];
            mysheet.DefaultRowHeight = 20 * 20;

            //3.创建Row中的Cell并赋值  
            mysheet.Cells[1, 1].Value = ("中文");
            mysheet.Cells[1, 2].Value = ("原始字体");

            mysheet.Cells[2, 1].Value = ("中文");
            mysheet.Cells[2, 2].Value = ("Boldweight");
            mysheet.Cells[2, 1].Style.Font.Bold = true;
            mysheet.Cells[2, 2].Style.Font.Bold = true;

            mysheet.Cells[3, 1].Value = ("中文");
            mysheet.Cells[3, 2].Value = ("Color");
            mysheet.Cells[3, 1].Style.Font.Color.SetColor(Color.Red);
            mysheet.Cells[3, 2].Style.Font.Color.SetColor(Color.Red);

            mysheet.Cells[4, 1].Value = ("中文");
            mysheet.Cells[4, 1].Value = ("Family");
            mysheet.Cells[1, 1].Style.Font.Family = 1;
            mysheet.Cells[1, 1].Style.Font.Family = 1;

            mysheet.Cells[5, 1].Value = ("中文");
            mysheet.Cells[5, 2].Value = ("Scheme");
            mysheet.Cells[5, 1].Style.Font.Scheme = "微软雅黑";
            mysheet.Cells[5, 2].Style.Font.Scheme = "微软雅黑";

            mysheet.Cells[6, 1].Value = ("中文");
            mysheet.Cells[6, 1].Value = ("FontName");
            mysheet.Cells[6, 1].Style.Font.Name = "微软雅黑";
            mysheet.Cells[6, 2].Style.Font.Name = "微软雅黑";

            mysheet.Cells[7, 1].Value = ("中文");
            mysheet.Cells[7, 2].Value = ("IsBold");
            mysheet.Cells[7, 1].Style.Font.Bold = true;
            mysheet.Cells[7, 2].Style.Font.Bold = true;

            mysheet.Cells[8, 1].Value = ("中文");
            mysheet.Cells[8, 2].Value = ("IsItalic");
            mysheet.Cells[8, 1].Style.Font.Italic = true;
            mysheet.Cells[8, 2].Style.Font.Italic = true;

            mysheet.Cells[9, 1].Value = ("中文");
            mysheet.Cells[9, 2].Value = ("IsStrikeout");
            mysheet.Cells[9, 1].Style.Font.Strike = true;
            mysheet.Cells[9, 2].Style.Font.Strike = true;

            mysheet.Cells[10, 1].Value = ("中文");
            mysheet.Cells[10, 2].Value = ("TypeOffset");

            mysheet.Cells[11, 1].Value = ("中文");
            mysheet.Cells[11, 2].Value = ("Underline");
            mysheet.Cells[11, 1].Style.Font.UnderLine = true;
            mysheet.Cells[11, 2].Style.Font.UnderLine = true;


            //4.保存         
            FileStream file = new FileStream(@"E:\myworkbook6.xlsx", FileMode.Create);
            package.SaveAs(file);
            file.Close();
        }
        #endregion

        #region 7.设置数字格式
        public void Test7()
        {

            //说明：设置数字格式  

            //1.创建EXCEL中的Workbook           
            ExcelPackage package = new ExcelPackage();

            //2.创建Workbook中的Sheet          
            ExcelWorksheet mysheet = package.Workbook.Worksheets[1];

            //3.创建Row中的Cell并赋值  
            mysheet.Cells[1, 1].Value = (2013.143);
            mysheet.Cells[1, 2].Value = ("转化为汉字大写");
            mysheet.Cells[1, 1].Style.Numberformat.Format = "[DbNum2][$-804]General";

            mysheet.Cells[2, 1].Value = (123152013.143);
            mysheet.Cells[2, 2].Value = ("改变小数精度");
            mysheet.Cells[2, 1].Style.Numberformat.Format = "0.0";

            mysheet.Cells[3, 1].Value = (123152013.143);
            mysheet.Cells[3, 2].Value = ("分段添加，号");
            mysheet.Cells[2, 1].Style.Numberformat.Format = "#,##0.0";

            mysheet.Cells[4, 1].Value = (123152013.143);
            mysheet.Cells[4, 2].Value = ("科学计数法");
            mysheet.Cells[2, 1].Style.Numberformat.Format = "0.00E+00";

            mysheet.Cells[5, 1].Value = (-123152013.143);
            mysheet.Cells[5, 2].Value = ("正数与负数的区分(负数红色)");
            mysheet.Cells[2, 1].Style.Numberformat.Format = "0.00;[Red]-0.00";

            mysheet.Cells[6, 1].Value = (123152013.77);
            mysheet.Cells[6, 2].Value = ("整数部分+分数");
            mysheet.Cells[2, 1].Style.Numberformat.Format = "# ??/??";

            mysheet.Cells[7, 1].Value = (123152013.77);
            mysheet.Cells[7, 2].Value = ("分数");
            mysheet.Cells[7, 1].Style.Numberformat.Format = "??/??";

            mysheet.Cells[8, 1].Value = (0.333);
            mysheet.Cells[8, 2].Value = ("百分数");
            mysheet.Cells[8, 1].Style.Numberformat.Format = "0.00%";

            //4.保存         
            FileStream file = new FileStream(@"E:\myworkbook7.xlsx", FileMode.Create);
            package.SaveAs(file);
            file.Close();
        }
        #endregion

        #region 8.设置EXCEL单元格【时间格式】
        public void Test8()
        {

            //说明：设置时间格式  
            //1.创建EXCEL中的Workbook           
            ExcelPackage package = new ExcelPackage();

            //2.创建Workbook中的Sheet          
            ExcelWorksheet mysheet = package.Workbook.Worksheets[1];


            //3.创建Row中的Cell并赋值  
            mysheet.Cells[1, 1].Value = (DateTime.Now);
            mysheet.Cells[2, 1].Value = (DateTime.Now);
            mysheet.Cells[3, 1].Value = (DateTime.Now);
            mysheet.Cells[4, 1].Value = (DateTime.Now);


            //【Tips】  
            // 1.yyyy 年份；    yy 年份后两位  
            // 2.MM 月份零起始；M 月份非零起始;  mmm[英文月份简写];mmmm[英文月份全称]  
            // 3.dd   日零起始；d 日非零起始  
            // 4.hh 小时零起始；h 小时非零起始[用于12小时制][12小时制必须在时间后面添加 AM/PM 或 上午/下午]  
            // 5.HH 小时零起始；H 小时非零起始[用于24小时制]  
            // 6.mm 分钟零起始；m 分钟非零起始  
            // 7.ss 秒数零起始；s 秒数非零起始  
            // 8.dddd 星期；ddd 星期缩写【英文】  
            // 9.aaaa 星期；aaa 星期缩写【中文】  


            mysheet.Cells[1, 1].Style.Numberformat.Format = "yyyy年MM月dd日 aaaa";
            mysheet.Cells[2, 1].Style.Numberformat.Format = "yyyy年MM月dd日 dddd";
            mysheet.Cells[3, 1].Style.Numberformat.Format = "h:mm:ss AM/PM";
            mysheet.Cells[4, 1].Style.Numberformat.Format = "h:mm:ss 上午/下午";


            //4.保存         
            FileStream file = new FileStream(@"E:\myworkbook8.xlsx", FileMode.Create);
            package.SaveAs(file);
            file.Close();
        }
        #endregion

        #region 9.设置文本格式
        public void Test9()
        {
            //说明：设置文本格式  
            //1.创建EXCEL中的Workbook           
            ExcelPackage package = new ExcelPackage();

            //2.创建Workbook中的Sheet          
            ExcelWorksheet mysheet = package.Workbook.Worksheets[1];


            //3.创建Row中的Cell并赋值  
            mysheet.Cells[1, 1].Value = ("130925199662080044");
            mysheet.Cells[1, 1].Style.Numberformat.Format = "@";
            mysheet.Cells[2, 1].Value = ("" + DateTime.Now + "");
            mysheet.Cells[2, 1].Style.Numberformat.Format = "text";


            //6.保存         
            var filename = "myworkbook9.xlsx";
            SaveExcel(package, filename);
        }
        #endregion

        #region 10.向EXCEL插入图片
        private void AddImage(ExcelWorksheet oSheet, int rowIndex, int colIndex, Bitmap image)
        {

            ExcelPicture excelImage = null;
            if (image != null)
            {
                excelImage = oSheet.Drawings.AddPicture("Debopam Pal", image);
                excelImage.From.Column = colIndex;
                excelImage.From.Row = rowIndex;
                excelImage.SetSize(100, 100);
                // 2x2 px space for better alignment
                excelImage.From.ColumnOff = Pixel2MTU(2);
                excelImage.From.RowOff = Pixel2MTU(2);
            }
        }
        public int Pixel2MTU(int pixels)
        {
            int mtus = pixels * 9525;
            return mtus;
        }
        public void Test10()
        {
            //说明：插入图片  

            //1.创建EXCEL中的Workbook           
            ExcelPackage package = new ExcelPackage();

            //2.创建Workbook中的Sheet          
            ExcelWorksheet mysheet = package.Workbook.Worksheets.Add("Sheet1");

            /* //http://cn.bing.com/HPImageArchive.aspx?format=js&idx=0&n=1  获取cnbing背景图片
             //第一步：读取图片到byte数组  
             WebRequest request = WebRequest.Create("http://s.cn.bing.net/az/hprichbg/rb/CavernduPontdArc_EN-US9994344414_1920x1080.jpg");
             byte[] bytes;
             MemoryStream mstream = new MemoryStream();
             using (Stream stream = request.GetResponse().GetResponseStream())
             {

                     int count = 0;
                     byte[] buffer = new byte[1024];
                     int readNum = 0;
                     while ((readNum = stream.Read(buffer, 0, 1024)) > 0)
                     {
                         count = count + readNum;
                         mstream.Write(buffer, 0, 1024);
                     }          
             }*/

            var base64str = "/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAsLDBkSEw8UHRofHh0aHBwgJC4nICIsIxwcKDcpLDAxNDQ0Hyc5PTgyPC4zNDL/2wBDAQkJCQwLDBgNDRgyIRwhMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjL/wAARCADNAaUDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD3+iiigAooooAKKKKACiiigAooooAKKKKACiiigAorhvil4V8ReMPDkWk6DqNraI8ubtbhmUSoOg3KpPXnGOfXjnxjw7eeJ/hD8SdN8O6lqCzafdPGJYI5maApKdu9QwG1gR1wCduOhoA+oKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAr5i+MzST/GzT4oTulVLVEA6hi5IH6/rX07XzF4l/4mH7UMChhtTU7Pqf7iRkj81NAH07RRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABTZJEiieSRgqIpZmPQAdTTq53x5dmy8Daw6kh5bc26EdmlIjU/m4oATwFrd94j8Fadq+opEl1dCR2WJSqgeYwXAJP8IHeujrn/AsH2bwFoEeME6fC7D0ZkDH9Sa6CgAr5qu40/wCGqlXGV+3Rt17+SD/OvpWvmfwq76h+1FdTP8/l6hejPXhEkRf5CgD6YooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKiluYIGVZp44y3QO4GfzqWvE/iJ8E9W8X+MbnW7LWrZYrhUBiug2YtqhcKQDkcZ7dfxoA9qSRJUDxurqejKcg06vm2L4DeN9AZdS0TXLMahDygt5nic+wYjHPocCvfvDn9r/8ACO2H9v8Ak/2sIQLnycbS/rxxn1xxnOOKANSvLfjfqs0Gh6bpFs5Se+mkm/3lgjL4+u/y/wAq9Srxbx7JFrfxi03S3ZnisYLVNo6LLNdR7x+MINAHsdpbpZ2cFrH9yGNY1+gGBU1FeE+Mf2hhp+pXGn+GtNgufIkMZvLpyY3IJB2opBI9G3fhQB7tXzR8Mwp/aH1Ungi4vio99zf/AF6dZftG+Jra6U6pommzwckpEJIXPphizD/x3tWX8LNVj1P4+rqEMbQxahcXkqRnqqskjgH6UAfVNFFFABRRSO6xozuwVVGWYnAA9aAForntF8deGPEWq3GmaRrNvd3kAJeOPPIBwSpIww91JroaACiiigAooooAKKKKACiiigAooooAKKK8u+Dut/2teeNU80yKNcluEyScJISFx7fJQB6jRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABXh/hiU638WNT1OQI8c/iBoYnXkFLS1mTj2/eQmvYdc1IaN4f1LVGXeLO1luCv97YpbH6V5H8GLFIrbw47bjLLY6hfybv77zwxKf++Yz+dAHtdeG+EvhzrXhb45XV5DpyN4fcTSR3JClURxlVXJyGVsL64BPQ17lUV1dQWVpLdXUyQ28KF5JZGwqKOSSewoA4j4yTWMHwt1h72KGQlFSBZAMiVmABXPcZJ45wDXzd8KL3+z/il4fmz9658n/v4pT/ANmrpfGPibWfjN42t9C0GNv7NikP2aNsquOjTy+nHT0BwBknNHXfD1t8PPjFodmjs1raz2M5lbjfgrvb2yyscdqAPraiiigArw348/ESG201vCWk3Ya7nP8AxMGjP+rj/wCeZPqxxkegwfvU74sfGhdM8/w/4WnV73lLm/Q5EHqsZ7v6t/D25+74JrWiaroz2r6vBJBNfQ/akWU/OULEBmB5BJU9ee/egD2H4AeBL1tTXxjd7obONHjs17zMcqzf7o5Hufpz9FVzvgCJIfh14bWNQqnTLdiB6mNST+JJNdFQAUUUUAFFFFABRRRQAUUUUAFFFFADXcRxs56KCTXz9+zVdZvfE0DcvKlvLn6GQH/0IV7zqRxpd2c4/cv/AOgmvnb9mz/kaNaGf+XJeP8AgYoA+hNZ1ix0DR7rVdSnENnbJvkc/kAPUk4AHckV86av+0V4jn1RpNIsLG2sEfKRzxtI7r/tkMP/AB3GM4yetaX7QPiW41PXtO8G6cWk8srLPFHyZJn4jTHqFOffePSvWvAfgTTvBvhKPSvIhmuJ486hKV3ee5HIORygyQB6e5OQDnPhx8ZdO8ZyJpmpRR6drJHyIG/dXH+4TyD/ALJz7E849Rr5p+L3wpPhib/hJ/DSSR6fvDzwRE5tHzw6EchM/wDfJ9jxUtf2h/Fltp9nbCx024lhj2zT3COzTEH73ysoBxjPXnJ9qAPqGiuT+Hvjq18feHTqUNu1rNFKYbiBnDbGAByD3BBHOB3HausoAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACoLy8tdPs5bu9uIre2iXdJLK4VVHqSeBU9fJfxY8f3vjnxQ2l6c8r6RbTeVawRAn7RJnb5mB94k8L6DsCTQB6nrn7RPhmweSLSbG91SRCAJMCCJx3wWy35rWt4A+M+keNtQ/sya0fTNSbJhieUSJKByQrYHzdTgj6ZrD8CfAbRLXRIbnxZateapMu97fz2WO39F+Qjc2OuSRngdMny74s+HYPAXxIibQibSJ4or62WMn9w24jgn/aQt7Zx2oA9t+O2rjTPhhd24LCXUJ47VCp6c72/DahH41peDtKGla6unsF8zTPD+n2rbem8tMX/AD2Ka868Qa1D8T/il4L0aBR9ht4I7+6QgsNzoszIexG1UXPYuRXuNvplva6ne6hHv8+8EYlLNkYQEKAO3U/nQBdr5h+KvjbUvHni/wD4RLQmY6dBP5IRWwLiVT8zsemxcHGeAAWPt9PV87+Jv2dtVuNfurnQNR09bCZzIkV20iNFkk7RtVsgdjwf5kA3fA2o/Db4WWRtLjxPZ3esXSg3V1bo8yeyKyKwVR7nJPJxwBxP7Qc8Vx410fUbSVJbefSo2imjYMr4kkOQR14Iqh48+ELeAfBttq13qy3V7NdpbmGKLbGgKOxwxOW+6OcDvxXMas003w58NySszCK8voIs/wAKAQOAP+BO5/GgD7TWeJrcXAkXySm8SZ+XbjOc+mK+ePip8ZZdVkk8N+EZX+zs3lT30X3rgnjZH32/7Q5btx97l/EfxO1vxdomkeEdFgnjt1tYLaZIxumvJQihhx/DkHAHXqfQeufCr4QW/hGOPWNbSO411lyicMloD2X1f1b8B6kAxvhP8F10vyPEHimAPfcPa2LjIg9HkHd/Qfw9+fu8j+0f/wAlA08f9QqP/wBGy19PV80ftI2zr4x0m6I+STT/ACwfdZGJ/wDQxQB7z4IG3wD4cX00u2H/AJCWt6qmmWgsNJs7NelvAkQ/4CoH9Kt0AFFFFABRRRQAUUUUAFFFFABRRRQAyaJZ4JIXGUkUqw9iMV8xfs+Xaad4/wBSt7qQQhtOkBDnHzI6E/kAx/CvqCviC01iTSvEer3gyzyw3cB5/wCeqtHn8N+fwoA9F+FlpJ49+M1/4mvI2a3tZHvcOMhXY4hTP+yOR/1zr6Yd1jQu7BVUZLMcAV5R+z7oI03wA+qOq+dqlw0gYdfLQlFB/wCBBz/wKud/aOudESLTLd7fzddddySeY2IYATn5c4yzcA4P3W9BQAz42/FO0uNObwv4dvkuPO/4/wC5t3ygT/nkrDg5/ix2GOckDwAI+wyBTsBClscAnoP0P5V6R8HPh7D438QTT6pEz6NYrmZQxXzXb7qZBBHdiR6Ad66Tx78BLzSoptR8JyTX1qATJYvzMo6/IR98e2M8DG40AegfAWXQj8Pli0l2+2LMW1FJGy4lIAB/3Cqjb9D3zXqNfFXw/wDGd34F8VQalHua1Y+VeQD/AJaRE8/8CHUe49Ca+z7S7t7+ygvLWVZbeeNZYpF6OrDII+oNAE1FFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABXHaN8L/Ceg+JZtesdO23kjFkDuWSAnqY1/hz+nQYHFdjRQAV8w/tH/8AJQNPH/UKj/8ARstfT1eTfFn4S33jzVrHVNLv7aC4hg+zyx3O4KUDMwIKgnOWPGPSgDlP2bdCL3Os+IZEbairZQt2JOHf8RiP/vqvoSua8B+ELfwR4UttGhkE0is0k84Xb5sjdTj6YA9lFdLQAUUUUAfPX7SPiBJLjR/DsMpJiDXlwg6An5Y/xx5nHow9a8bv/EF1f6BpWissSWem+aYwi4Z3kbczMe56Aeyj3rrtX8G+OPGvxJ1NLjSLlL2a6bzZZUZYIUBwvz4xsCgYxkkAYzXc/EP4XaV4M+DzGyUT30N3DLdXrrh5M5TA/urlxwPxyeaAOl+A/g/SLLwfaeJ1i83VL4SAzSc+UqyMm1PTO3k9Tn0r12vL/gDfG7+F8MB6Wd3NAPxIk/8AaleoUAFeK/tHaFNe+GdM1qFWZdPnaOYKv3Ulx8xPoGRR/wACr2qorq1gvbWW1uoY5reZCkkUihldTwQQeooAoeG9Yi8QeGtN1aF0Zbu3SU7WBCsR8y8dwcg+hBrUrzy0+D2kaVcSvouv+JdJglfebWy1DZFn6FST+JJroY/BWlExtey6jqLIwYfbr+aVcjp+73bP/HaAOiooooAKKKKACiiigAooooAKKKKACvhXWtPnt/Feo6YELTpeyW+0dSwcrj86+6q4i6+FHhm78dR+LnjuVvklWcwpIFheVejlcZzkA8EAkcg5OQDp9B0qPQvD+naTEQyWdtHAGAxu2qATj1OM/jXzZ8WvDHiPX/jHd29npt5cLcrAtq4jPl7PLUE7ugUNuyeg5r6jqrqWpWWj6dPqGo3MdtaQLvllkOAo/wAc8AdSTgUAY/gfwla+CvCtro1sQ7oPMuJsY82U/eb6cAD2AFdFXgfiP9pBI5ZIfDWjCVVPy3V8xAb1xGvOPQlgfauQb4y/EvxDeeVpLbHYYEGnWAkP4bg7frQB2Xx68AaRBpUvi+y2Wt8Z0S5i3BUuN2RuC95M4Jx1AYkdTXRfs+apcX/w5ktp2LLY3skEJI6IVV8Z78u34Yrye3+HXxO8e3kUusJfpEGI8/V5mURZ5OEb5hnH8K4r6N8EeD7LwP4Zh0eydpSGMs87DBmlOMtjsOAAOwA5J5IB0VFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAVwvxkga5+E2vIgJIjjfj0WVGP6Cu6rzf46i5/wCFU6i0EhRFlhMwB+8hkAx/30V/KgDnv2bb1X8I6vYjG+G/84/R41A/9Fmvaa+fP2Z7hFuPElsW/eOlvIq+ymQE/wDjy19B0AFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAV8yfG/xbeeKPGcXhHSy0ttZzLD5URz5903GPfbnYB2O71r6H8R61F4d8N6lrEwUpZ27yhWON7AfKufc4H4185fAfQ5PEXxDutev90409GuHkfB3XEhIUnPf/WNn1UUAeteBvg74d8L6XC2o2NtqerMoM89wgkRG4+WNWGAAR97G48/Qegz3FtYWjz3E0NtbQrlpJGCIij1J4Aqrret6d4d0mfVNUuUt7SFcs7HqewA7k9gOtfInxB+Imq+PdVkklkeHS4nzbWYb5UHQM3q59e2TjigD7A07VdO1i3Nxpl/a3sAbYZLaZZFDemVJGeRVuvnb9mq3vDqevXSuwsRDFG69mkLEqfqAG/7696+iaACioL28t9Psbi9u5RFbW8bSyyHoqqMk/kK8Fv8A46eML4XGoeHPCQbRYnKi5uLaabp3ZkIVTgg45xnqaAPoGivnvQP2kpxNs8R6JE0RP+u05ipUf7jk7v8AvoV7J4Y8beHvGEDSaJqUVw6DMkByksfTqh5xzjI496AOgooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigArivi7ZSX/wq8QQxjLLAsx+kbq5/RTXa1jeLbWS+8Ga7ZwqWkn0+4iQDuWjYD+dAHz1+zjKV8fahFuwr6Y5xnqRLH/ia+nq+RvgZcPD8WdKjUkCeOeNvceUzfzUV9c0AFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQB4/wDtEa9/Z/gi10eNyJdTuRuGODFHhm57HcY/1rN+F2qaN8N/hJ/b+tTKkuqzySxRIv76ZU+RUUd+QWz0G/k1w/7QOsvqHxHOn/OI9Nto4tpPBZx5hYD3DKP+A1l+EPCPiD4ra1CJp5I9MsYo7ZrlhlLeJAAsaDu2O3uSevIBZvL7xb8cfGCwQR+XaxHKQhj5FnGf4mPdj64yegAHAxvH2n6boeuJ4V0R2uU0/Ed1cYG66uj988dAvCBf4SG7sSfovX59F+DXw0nOj2yRSf6q23/M89wwOHc/xEAFj0GFwMcCvGPgf4Vl8UePDrV8HltdMYXMkjnJkuCSUBOc5zl88/d560AfQXw+8JReCvBtnpKgG4x5124Od8zAbj9BgKPZRXCeKPj3a+HPGlxoq6M91aWknlXFwJtr7uN21SuDt5HJGT3Fex1wHir4O+FfF2ttq96t5b3cmPONrMFE2AACwIPOABxigDe8RaTa+O/BVzp0GoNDbahEhS5hG7A3BunfOMEe5rR0LRrPw7oVlpFgm22tIhGmcZb1Y47k5J9yaTQ9B0zw1pUemaRaLa2cZJWMMW5JySSSST9TWjQBxHi/4U+FfGLPcXdmbS/bk3lnhJGP+0MEN9SM+4rwzxN8HPF/geZtY0a5e+trUGQXdmxinhA6kpnPc/dLcAk4r6qooA83+Dfjy78beGZ11P5tS0+RY5ZQuBKrDKtxwDwQQPTPevSKqWGl6dpUckenWFrZxyOZHW3hWMMx6sQoGT71boAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooA+NvARuPDnxh0i3bKzwan9ikH+8xiYf+PGvsmvj7xVu8M/HW8upx/x760t9j/ZaQSj9GFfYNABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAeZ+NvgvpHjTxRFrcl/cWbuFW8jiQN54UAAgn7jYAGcEYA46573RtF07w/pUGmaXapbWkC4SNP1JPUk9yeTV+igD5o/aN1s3fivTdGR1MdjbGVwOokkPQ/8BVCP96vZPhX4cj8M/DrSrUJtuLiIXdySuCZJADgj1UbV/wCA14f+0F4cutP8cDXNjtZalEgEmPlWRFClP++VVvfJ9DXa6L+0bof9kWy61pmorqCoFmNrHG0bMP4ly6kZ64xx0yetAHt1FeLah+0j4eiti2naLqdxcZ4S4McKY/3gzn9K831r45eN9dl8myni02N/lEVjFl254+Zstnt8uPpQB9Y0Vxnwvm8Tz+CYJPFqSrqJlfZ567ZTFxt3jsc5684xXZ0AFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQB8m/Hqwaz+Kd1Oel5bQzr9Avl/zjNfVdndR31jb3cJzFPGsqH1DDI/nXzh+0lZuni/SL4/6uaw8kfVJGJ/9GCvdfAcgl+HnhtlYN/xK7YEg9xEoP60AdDRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFAFHV9H07XtNm07VbOK7tJRh4pBx9QeoI7EcivMrj9nXwbNO8kd3rECsciKO4jKr7DdGT+ZNet0UAeTWn7PHgu2uUlln1a6RTkwzXCBW9jsRW/IivQdC8KaB4ZiEejaTaWfy7DJHGPMYZzhnPzNz6k1sUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAeB/tMwu1v4ZnCny0e5Rj6EiIj/wBBNegfBmZ5/hJoLyMSwSVAT6LM6j9AK5v9o5Afh/p77RuXVIxn0Bil/wDrVe/Z/v3vPhikDdLK8mgX6HEn85DQB6lRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAcZ8UfCVx4z8C3WmWXl/bkdJ7cSNtUsp5GfdSwHbJGcdapfB7wfqngrwZLYauIluri7a5Mcb7vLBRF2k9M/IemRz1r0CigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigD/2Q==";
            byte[] buffer = Convert.FromBase64String(base64str);
            MemoryStream mstream = new MemoryStream(buffer);
            Bitmap bitmap = new Bitmap(mstream);
            AddImage(mysheet, 3, 2, bitmap);
         /*   
          //第二步：将图片添加到workbook中  指定图片格式 返回图片所在workbook->Picture数组中的索引地址（从1开始）   
            ExcelPicture picture = mysheet.Drawings.AddPicture("logo", Image.FromStream(mstream));//插入图片

            //第四步：设置锚点 （在起始单元格的X坐标0-1023，Y的坐标0-255，在终止单元格的X坐标0-1023，Y的坐标0-255，起始单元格行数，列数，终止单元格行数，列数）  
            picture.SetPosition(100, 100);//设置图片的位置
            picture.From.Column = 2;
            picture.From.Row = 3;
            //设置图片的大小
            picture.SetSize(100, 100);*/

            //6.保存         
            var filename = "myworkbook10.xlsx";
            SaveExcel(package, filename);
        }
        #endregion

        private void SaveExcel(ExcelPackage package, string excelFileName)
        {
            string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, excelFileName);
            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate))
            {
                package.SaveAs(fs);
            }
        }
        private string GetMegerValue(ExcelWorksheet wSheet, int row, int column)
        {
            string range = wSheet.MergedCells[row, column];
            if (range == null)
            {
                if (wSheet.Cells[row, column].Value != null)
                    return wSheet.Cells[row, column].Value.ToString();
                else
                    return "";
            }
            object value = wSheet.Cells[(new ExcelAddress(range)).Start.Row, (new ExcelAddress(range)).Start.Column].Value;
            if (value != null)
                return value.ToString();
            else
                return "";
        }
    }
}
